﻿#region Using Directive
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Resources;
using System.Configuration;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using System.IO;

using MSBIHelper.Helper;
#endregion

/*************************************************************************************
 * Feature: 
 * Add/Remove Notification Framework when the package occurs wrong
 *  Add scripttask(c#) when Error occur ,capture and update the errorinfo
 *  Add scripttask(c#) when package finished, then send the mail out
 * 
 * 
 * 
 * 
 * 
 * 
 * 
 
 * *************************************************************************************/

// ssis package load save 
// process information need to store 
// one flag switch the function off/on variable


namespace MSBIHelper
{

    public static class SSISHelper
    {
        #region Member
        static string _SMTPServer { get { return ConfigurationManager.AppSettings["SMTPServer"].ToString(); } }
        static string _MailFrom { get { return ConfigurationManager.AppSettings["SSISFrom"].ToString(); } }
        static string _MailTo { get { return ConfigurationManager.AppSettings["SSISTo"].ToString(); } }
        static string _CSVFilePath = ConfigurationManager.AppSettings["MSBIHelper_CSVFilePath"].ToString();
        static string _GenerateDailyFolder = ConfigurationManager.AppSettings["GenarateDailyFolder"].ToString();
        const string _scriptlanguage = "Microsoft Visual C# 2008";
        const string _Notification_varNamePrefix = "varmsbihelper_Notification_";
        const string _varName_smtpserver = _Notification_varNamePrefix + "SMTPSERVER";
        const string _varName_mailfrom = _Notification_varNamePrefix + "MAILFROM";
        const string _varName_mailto = _Notification_varNamePrefix + "MAILTO";
        const string _varName_DictError = _Notification_varNamePrefix + "Dict";
        const string _Audit_TaskNamePrefix = "MSBIHelper_Audit_";
        const string _Audit_varNamePrefix = "varmsbihelper_Audit_";
        static Application _packageAPP = new Application();
        static ResourceManager _rm = new ResourceManager(typeof(MyResource));
        #endregion

        #region Common Functions

        static string GetStringFromResource(string sourceName)
        {
            return _rm.GetString(sourceName);
        }
        static Package GetPackage(string fileName)
        {
            return _packageAPP.LoadPackage(fileName, null);
        }
        static void SavePackage(string fileName, Package p)
        {
            _packageAPP.SaveToXml(fileName, p, null);
        }
        static int CountVariables(Package p, string pattern)
        {
            int count = 0;
            foreach (var v in p.Variables)
            {
                if (
                    v.Namespace == "User"
                    &&
                    v.Name.StartsWith(pattern, StringComparison.CurrentCultureIgnoreCase))
                    count++;
            }
            return count;
        }
        struct sEventType
        {
            public static string OnWarning = "OnWarning";
            public static string OnError = "OnError";
            public static string OnPostExecute = "OnPostExecute";
            public static string OnPreExecute = "OnPreExecute";
        }
        static DtsEventHandler GetEventHandler(Package p, string ehName)
        {
            DtsEventHandler eh = null;
            if (p.EventHandlers.Contains(ehName))
                eh = p.EventHandlers[ehName] as DtsEventHandler;
            else
                eh = p.EventHandlers.Add(ehName) as DtsEventHandler;
            return eh;
        }
        static void AddScriptTask(DtsEventHandler eh, string taskName, string codeText)
        {
            if (eh.Executables.Contains(taskName))
                eh.Executables.Remove(taskName);
            Executable e = eh.Executables.Add("STOCK:ScriptTask");
            TaskHost myst = e as TaskHost;
            myst.Name = taskName;
            ScriptTask st = myst.InnerObject as ScriptTask;
            try
            {
                string guid = Guid.NewGuid().ToString().Replace("-", "");
                st.ScriptProjectName = "ST_" + guid;
                st.ScriptLanguage = _scriptlanguage;
                st.ScriptingEngine.ShowDesigner(false);
                st.ScriptingEngine.InitNewScript("CSharp", st.ScriptProjectName, ".csproj");
                st.ScriptingEngine.AddCodeFile("ScriptMain.cs", codeText.Replace("*ProjectName*", guid));
                if (!st.ScriptingEngine.Build())
                    throw new Exception("ScriptTask build fail! check the script code!");
                else
                    st.ScriptingEngine.SaveScriptToStorage();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                st.ScriptingEngine.CloseIDE(true);
            }
        }
        static void AddVariable(Package p, string vName, object vValue)
        {
            if (p.Variables.Contains(vName))
                p.Variables[vName].Value = vValue;
            else
                p.Variables.Add(vName, false, "User", vValue);
        }
        static void RemoveVariables(Package p, string varPattern)
        {
            List<string> variableList = new List<string>();
            foreach (var var in p.Variables)
            {
                if (var.Name.StartsWith(varPattern))
                    variableList.Add(var.Name);
            }
            foreach (var item in variableList)
            {
                p.Variables.Remove(item);
            }
        }
        static void RemoveScriptTask(Package p, string ehName, string taskName)
        {
            DtsEventHandler eh = GetEventHandler(p, ehName);
            if (null == eh) return;

            for (int i = 0; i < eh.Executables.Count; i++)
            {
                if ((eh.Executables[i] as TaskHost).Name == taskName)
                    eh.Executables.Remove(i);
            }

            if (eh.Executables.Count.Equals(0))
                p.EventHandlers.Remove(eh.Name);
        }
        #endregion

        #region Notification Framework

        public static string AddNotificationFramework(string filename, bool isAdd)
        {
            string rst = string.Empty;
            try
            {
                Package p = GetPackage(filename);
                if (isAdd)
                {
                    Notification_Add(p, ref rst);
                    if (string.IsNullOrEmpty(rst))
                    {
                        SavePackage(filename, p);
                        rst += "EXITCODE:0 Notification Framework add Success for package:" + filename + Environment.NewLine;
                    }
                }
                else
                {
                    Notification_Remove(p, ref rst);
                    if (string.IsNullOrEmpty(rst))
                    {
                        SavePackage(filename, p);
                        rst += "EXITCODE:0 Notification Framework sucessfully remove for package:" + filename + Environment.NewLine;
                    }
                }
            }
            catch (Exception EX)
            {
                rst = "EXITCODE:1 Notification Framework add fail for Package:" + filename;
                rst += Environment.NewLine;
                rst += "Exception information:" + Environment.NewLine;
                rst += EX.Message;
            }

            return rst;
        }
        private static void Notification_Add(Package p, ref string rst)
        {
            if (CountVariables(p, _Notification_varNamePrefix) > 0)
            {
                rst += "EXITCODE:0 Notification Framework has added before for package:"
                    + p.Name + Environment.NewLine;
                return;
            }
            Notification_AddVariables(p);
            Notification_AddScriptForEvent(p, sEventType.OnError);
            Notification_AddScriptForEvent(p, sEventType.OnPostExecute);
        }

        private static void Notification_Remove(Package p, ref string rst)
        {
            if (CountVariables(p, _Notification_varNamePrefix) == 0)
            {
                rst += "EXITCODE:0 Notification Framework has not added before for Package:"
                  + p.Name + Environment.NewLine;
                return;
            }
            RemoveVariables(p, _Notification_varNamePrefix);
            RemoveScriptTask(p, sEventType.OnError, "msbihelperEhObj_OnError_Notification");
            RemoveScriptTask(p, sEventType.OnPostExecute, "msbihelperEhObj_OnPostExecute_Notification");
        }

        static void Notification_AddScriptForEvent(Package p, string ehName)
        {
            DtsEventHandler eh = GetEventHandler(p, ehName);
            string CodeText = GetStringFromResource("Notification_" + ehName);
            CodeText = CodeText.Replace("$smtpserver$", _varName_smtpserver)
                               .Replace("$mailfrom$", _varName_mailfrom)
                               .Replace("$mailto$", _varName_mailto);
            CodeText = CodeText.Replace("$var_dict_error$", _varName_DictError);
            AddScriptTask(eh, "msbihelperEhObj_" + eh.Name + "_Notification", CodeText);
        }

        static void Notification_AddVariables(Package p)
        {
            AddVariable(p, _Notification_varNamePrefix + "Flag", 1);
            AddVariable(p, _varName_smtpserver, _SMTPServer);
            AddVariable(p, _varName_mailfrom, _MailFrom);
            AddVariable(p, _varName_mailto, _MailTo);
            AddVariable(p, _varName_DictError, null);
        }
        #endregion

        #region Audit Framework
        delegate void dowork(Package p, TaskHost th, bool isAdd, StringBuilder sb = null);

        public static string AddAuditFramework(string filename, bool isAdd)
        {
            string rst = string.Empty;
            try
            {
                Package p = GetPackage(filename);
                if (isAdd && CountVariables(p, _Audit_varNamePrefix) > 0)
                { rst += "EXITCODE:0 Audit Framework has added before for package:" + filename + "\r\n"; return rst; }

                StringBuilder sbRC = new StringBuilder();
                traverseExecutables(p, p.Executables, isAdd, sbRC, ProcessPipeline);
                Audit_OnExecute(p, sEventType.OnPostExecute, isAdd, sbRC);
                Audit_OnExecute(p, sEventType.OnPreExecute, isAdd);
                Audit_OnAlert(p,sEventType.OnError, isAdd);
                Audit_OnAlert(p,sEventType.OnWarning,isAdd);
                SavePackage(filename, p);

                rst += isAdd ? "EXITCODE:0 Audit Framework has added success for Package:" + filename + "\r\n" :
                              "EXITCODE:0 Audit Framework has removed success for Package:" + filename + "\r\n";
            }
            catch (Exception EX)
            {
                rst = "EXITCODE:1 Notification Framework add fail for Package:" + filename;
                rst += Environment.NewLine;
                rst += "Exception information:" + Environment.NewLine;
                rst += EX.Message;
            }
            return rst;
        }

        static void Audit_ExecuteSqlTask(Package p, TaskHost EST, bool isAudit, StringBuilder sb)
        {
            if (EST.Disable) return;
            ExecuteSQLTask myest = EST.InnerObject as ExecuteSQLTask;
            if (EST.Name.ToLower().Contains("truncate")) return;
            if (myest == null) return;
            if (isAudit)
            {
                //Specify the ExecValueVariable value
                // code to write      
                string codetemplate = GetStringFromResource("Audit_RC_ScriptTemplate");
                if (codetemplate != string.Empty)
                {
                    int i = CountVariables(p, _Audit_varNamePrefix + "EST_RC_") + 1;
                    string vname = string.Format("{0}{1}", _Audit_varNamePrefix + "EST_RC_", i.ToString());
                    Variable v = p.Variables.Add(vname, false, "User", 0);
                    EST.ExecValueVariable = v;
                    string codetext = string.Format(
                        codetemplate,
                        EST.ID,
                        "Unknown",
                        "Unknown",
                        EST.Name,
                        "DEST",
                        EST.ID,
                        myest.SqlStatementSourceType.ToString().Replace("\"", " "),
                        myest.Connection.Replace("\"", " "),
                        myest.SqlStatementSource.Replace("\"", " "),
                        vname);
                    sb.AppendLine(codetext.Replace("^^", "{").Replace("@!@", "}"));
                }
            }
            else
            {
                if (EST.ExecValueVariable != null)
                {
                    Variable v = EST.ExecValueVariable;
                    EST.ExecValueVariable = null;
                }
                RemoveVariables(p, _Audit_varNamePrefix);
            }

        }

        //Warning or Error
        static void Audit_OnAlert(Package p, string ehName, bool isAdd)
        {
            string ehSTName = _Audit_TaskNamePrefix + ehName;
            DtsEventHandler eh = GetEventHandler(p,ehName);
            if (!isAdd && eh != null)
            {
                RemoveScriptTask(p,ehName, ehSTName);
                return;
            }
            string codetext = GetStringFromResource("Audit_OnError");
            codetext = codetext.Replace("//ReplaceHere", GetStringFromResource("csv_LogAlert"));
            codetext = codetext.Replace("@EventType@",ehName==sEventType.OnError? "E":"W");
            AddScriptTask(eh, ehSTName, codetext);

        }
  
        static void Audit_OnExecute(Package p,string ehName, bool isAdd,StringBuilder sb =null)
        {
            DtsEventHandler eh = GetEventHandler(p,ehName);
            if (!isAdd)
            {
                RemoveScriptTask(p,ehName, _Audit_TaskNamePrefix + ehName);
                RemoveVariables(p, _Audit_varNamePrefix);
                RemoveVariables(p, "MSBIHelper_");
                return;
            }
            else
            {
                if (!p.Variables.Contains("MSBIHelper_CSVFilePath")) p.Variables.Add("MSBIHelper_CSVFilePath", false, "User", _CSVFilePath);
                if (!p.Variables.Contains("MSBIHelper_GenarateDailyFolder")) p.Variables.Add("MSBIHelper_GenarateDailyFolder", false, "User", _GenerateDailyFolder);
            }

            string script = GetStringFromResource("Audit_"+ehName);
            if (ehName == sEventType.OnPreExecute)
            {
                script = script.Replace("//ReplaceHere", GetStringFromResource("csv_PreExecute"));
            }
            else
            {
                script =script.Replace("/*REPLACE YOUR CODE HERE PLEASE*/", sb.ToString());
                script = script.Replace("//ReplaceHereUpdatePkgInfo", GetStringFromResource("csv_PE_UpdatePkgInfo"));
                script = script.Replace("//ReplaceHereRowcountinfo", GetStringFromResource("csv_PE_UpdateRowInfo"));
            }
            AddScriptTask(eh, _Audit_TaskNamePrefix + ehName, script);
        }

        static void traverseExecutables(Package p, Executables executables, bool isAdd, StringBuilder sb, dowork dw)
        {
            foreach (Executable executable in executables)
            {
                TaskHost taskHost = executable as TaskHost;
                if (taskHost != null && !taskHost.Disable)
                {
                    if (taskHost.InnerObject is MainPipe)
                        dw(p, taskHost, isAdd, sb);
                    if (taskHost.InnerObject is ExecuteSQLTask)
                        Audit_ExecuteSqlTask(p, taskHost, isAdd, sb);
                    continue;
                }

                Sequence sequence = executable as Sequence;
                if (sequence != null && !sequence.Disable)
                {
                    traverseExecutables(p, sequence.Executables, isAdd, sb, dw);
                    continue;
                }

                ForLoop forLoop = executable as ForLoop;
                if (forLoop != null && !forLoop.Disable)
                {
                    traverseExecutables(p, forLoop.Executables, isAdd, sb, dw);
                    continue;
                }

                ForEachLoop forEachLoop = executable as ForEachLoop;
                if (forEachLoop != null && !forEachLoop.Disable)
                {
                    traverseExecutables(p, forEachLoop.Executables, isAdd, sb, dw);
                    continue;
                }
            }
        }

        static void ProcessPipeline(Package p, TaskHost th, bool isAdd, StringBuilder sb = null)
        {
            MainPipe pipeline = th.InnerObject as MainPipe;
            if (pipeline == null) return;
            List<IDTSComponentMetaData100> comlist = new List<IDTSComponentMetaData100>();
            foreach (IDTSComponentMetaData100 componentMetadata in pipeline.ComponentMetaDataCollection)
            {
                if (
                    componentMetadata.ObjectType.ToString().Equals("9")//source component
                    ||
                    componentMetadata.ObjectType.ToString().Equals("10")//destination component
                   )
                    comlist.Add(componentMetadata);
            }
            if (comlist.Count < 1) return;
            foreach (IDTSComponentMetaData100 com in comlist)
            {
                if (com.ObjectType.ToString().Equals("9"))//source task
                {
                    if (isAdd)
                        AddRowCountTask(p, th, com, true, sb);
                    else
                        RemoveRowCountTask(p, pipeline, com, true);
                }
                if (com.ObjectType.ToString().Equals("10"))//destination task add audit
                {
                    if (isAdd)
                        AddRowCountTask(p, th, com, false, sb);
                    else
                        RemoveRowCountTask(p, pipeline, com, false);
                }
            }
        }

        static void RemoveRowCountTask(Package p, MainPipe dataFlowTask, IDTSComponentMetaData100 com, bool isSource)
        {
            IDTSPath100 path1 = GetPath(dataFlowTask, com, isSource);
            //path1.EndPoint.Component should be the RowcountTask
            if (path1 == null) return;
            IDTSOutput100 startpoint = null;
            IDTSInput100 endpoint = null;
            IDTSComponentMetaData100 rowCountTask = null;
            IDTSComponentMetaData100 comAfter = null;
            if (isSource)
                rowCountTask = path1.EndPoint.Component;
            else
                rowCountTask = path1.StartPoint.Component;
            if (rowCountTask == null || (rowCountTask != null && !rowCountTask.Name.Contains(_Audit_TaskNamePrefix + "RC_"))) return;
            IDTSPath100 path2 = GetPath(dataFlowTask, rowCountTask, isSource);
            if (path2 == null) return;

            if (isSource)
            {
                startpoint = path1.StartPoint;
                comAfter = path2.EndPoint.Component;
            }
            else
            {
                startpoint = path2.StartPoint;
                comAfter = path1.EndPoint.Component;
            }
            dataFlowTask.PathCollection.RemoveObjectByID(path1.ID);
            dataFlowTask.PathCollection.RemoveObjectByID(path2.ID);
            foreach (IDTSInput100 item in comAfter.InputCollection)
            {
                if (!item.IsAttached) { endpoint = item; break; }
            }
            AddPath(dataFlowTask, startpoint, endpoint);
            dataFlowTask.ComponentMetaDataCollection.RemoveObjectByID(rowCountTask.ID);
            RemoveVariables(p, _Audit_varNamePrefix);
        }

        static void AddRowCountTask(Package p, TaskHost th, IDTSComponentMetaData100 com, bool isSource, StringBuilder sb)
        {
            string sourcetype = isSource ? "SRC" : "DEST";//BAD
            MainPipe dataFlowTask = th.InnerObject as MainPipe;
            int i = CountVariables(p, _Audit_varNamePrefix + "RC_") + 1;
            IDTSComponentMetaData100 rowcountTask = initialRowCountTask(p, dataFlowTask, com, i);
            sourcetype = LinkRowCountPath(p, dataFlowTask, com, rowcountTask, isSource);
            if (sb != null)
            {
                UpdateScriptInfo(sb, p, th, com, isSource, i, sourcetype);
            }
        }

        static Dictionary<string, string> GetDataObject(Package p, IDTSComponentMetaData100 com, bool isSource)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            string cmid = com.RuntimeConnectionCollection[0].ConnectionManagerID;
            ConnectionManager cm = p.Connections[cmid];
            if (cm == null) return dict;
            dict.Add("CMID", cm.ID);
            dict.Add("CMNAME", cm.Name);
            if (cm.Name.Equals("FLATFILE"))
                dict.Add("DATAOBJECTTYPE", "FLATFILE");
            else dict.Add("DATAOBJECTTYPE", cm.CreationName);

            if (cm.CreationName.Equals("OLEDB"))
            {
                if (isSource && com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("0"))
                    dict.Add("DATAOBJECT", com.CustomPropertyCollection["OpenRowset"].Value.ToString());
                if (!isSource && (com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("0")
                    || com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("3")
                   ))
                {
                    dict.Add("DATAOBJECT", com.CustomPropertyCollection["OpenRowset"].Value.ToString().Replace("\"", " "));
                    if (dict.ContainsKey("DATAOBJECTTYPE"))
                        dict["DATAOBJECTTYPE"] = "TableOrView";
                }
            }

            if (cm.CreationName.Equals("OLEDB")
                &&
                com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("2")
                )// 2 use the sql query so we can get the sqlcommand property value
            {
                //Replace the Query's special char such as "
                dict.Add("QUERY", com.CustomPropertyCollection["SqlCommand"].Value.ToString().Replace("\"", " "));
                if (dict.ContainsKey("DATAOBJECTTYPE"))
                    dict["DATAOBJECTTYPE"] = "Query";
            }

            return dict;
        }

        static void UpdateScriptInfo(StringBuilder sb, Package p, TaskHost th, IDTSComponentMetaData100 com, bool isSource, int i, string outputType)
        {
            string codetemplate = GetStringFromResource("Audit_RC_ScriptTemplate");
            if (!string.IsNullOrEmpty(codetemplate))
            {
                Dictionary<string, string> dict = GetDataObject(p, com, isSource);
                string cmid = string.Empty; string cmname = string.Empty;
                string dataobject = string.Empty; string query = string.Empty;
                string dataObjectType = string.Empty;
                if (dict.ContainsKey("CMID")) cmid = dict["CMID"];
                if (dict.ContainsKey("CMNAME")) cmname = dict["CMNAME"];
                if (dict.ContainsKey("DATAOBJECT")) dataobject = dict["DATAOBJECT"].Replace("\"", " ");
                if (dict.ContainsKey("QUERY")) query = dict["QUERY"];
                if (dict.ContainsKey("DATAOBJECTTYPE")) dataObjectType = dict["DATAOBJECTTYPE"];
                string codetext = string.Format(
                    codetemplate,
                    th.ID,
                    cmid,
                    cmname,
                    com.Name,
                    outputType,
                    th.ID + "_" + com.ID.ToString(),
                    dataObjectType,
                    dataobject,
                    query,
                    string.Format("{0}{1}", _Audit_varNamePrefix + "RC_", i.ToString())
                    );
                sb.AppendLine(codetext.Replace("^^", "{").Replace("@!@", "}"));
            }
        }

        static string LinkRowCountPath(Package p, MainPipe dataFlowTask, IDTSComponentMetaData100 com, IDTSComponentMetaData100 rowcountTask, bool isSource)
        {
            string outputtype = isSource ? "SRC" : "DEST";
            IDTSPath100 path = GetPath(dataFlowTask, com, isSource);
            IDTSOutput100 startpoint = null;
            IDTSInput100 endpoint = null;

            if (path != null)
            {
                startpoint = path.StartPoint;
                if (!isSource)
                    GetOutputType(dataFlowTask, com, ref outputtype);
                IDTSComponentMetaData100 comafter = path.EndPoint.Component;
                dataFlowTask.PathCollection.RemoveObjectByID(path.ID);
                foreach (IDTSInput100 item in comafter.InputCollection)
                {
                    if (!item.IsAttached) { endpoint = item; break; }
                }
                AddPath(dataFlowTask, startpoint, rowcountTask.InputCollection[0]);
                AddPath(dataFlowTask, rowcountTask.OutputCollection[0], endpoint);

            }
            return outputtype;
        }

        static void AddPath(MainPipe dataFlowTask, IDTSOutput100 startpoint, IDTSInput100 endpoint)
        {
            dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(startpoint, endpoint);
        }

        static void GetOutputType(MainPipe dft, IDTSComponentMetaData100 com, ref string outputtype)
        {
            IDTSPath100 path = GetPath(dft, com, false);
            if (path != null)
            {
                if (path.StartPoint.Component.ObjectType.ToString().Equals("9")
                    ||
                    path.StartPoint.Component.ObjectType.ToString().Equals("10")
                    )
                {
                    if (path.StartPoint.IsErrorOut)
                        outputtype = "BAD";
                    else outputtype = "DEST";
                    return;
                }
                else
                {
                    com = path.StartPoint.Component;
                    GetOutputType(dft, com, ref outputtype);
                }
            }
        }

        static IDTSPath100 GetPath(MainPipe dataFlowTask, IDTSComponentMetaData100 com, bool isSource)
        {
            foreach (IDTSPath100 path in dataFlowTask.PathCollection)
            {
                if (isSource && path.StartPoint.Component.ID.Equals(com.ID))
                {
                    if (path.StartPoint.IsErrorOut)
                        continue;// Just Care the Green output Not Error output ,if error just care the desitination
                    else
                        return path;
                }
                if (!isSource && path.EndPoint.Component.ID.Equals(com.ID))
                    return path;
            }

            return null;
        }

        static IDTSComponentMetaData100 initialRowCountTask(Package p, MainPipe dataFlowTask, IDTSComponentMetaData100 com, int i)
        {
            Variable var_rc = p.Variables.Add(_Audit_varNamePrefix + "RC_" + i.ToString(), false, "User", 0);
            IDTSComponentMetaData100 rowcountTask = dataFlowTask.ComponentMetaDataCollection.New();
            rowcountTask.ComponentClassID = "DTSTransform.RowCount";
            rowcountTask.Description = string.Format("{0}{1}", _Audit_TaskNamePrefix + "RC_", com.Name.Replace(" ", "_"));
            CManagedComponentWrapper wrapper = rowcountTask.Instantiate();
            wrapper.ProvideComponentProperties();
            wrapper.SetComponentProperty("VariableName", var_rc.Name);
            rowcountTask.Name = string.Format("{0}{1}", _Audit_TaskNamePrefix + "RC_", com.Name.Replace(" ", "_"));
            return rowcountTask;
        }
        #endregion

        #region Configuration
        public static string Config_UpdateVariables(ParameterObject _po)
        {
            string rst = string.Empty;
            try
            {
                Package p = GetPackage(_po._fileName);
                foreach (string row in _po._varNameValue.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries))
                {
                    if (row.IndexOf("$") == -1) continue;
                    if (p.Variables.Contains(row.Split('$')[0]))
                        p.Variables[row.Split('$')[0]].Value = row.Split('$')[1];
                }
                SavePackage(_po._fileName, p);
                rst += "EXITCODE:0 Configuration Update Variables has finished success for Package:" + _po._fileName + "\r\n";
                             
            }
            catch (Exception EX)
            {
                rst = "EXITCODE:1 Configuration Update Variables  fail for Package:" + _po._fileName;
                rst += Environment.NewLine;
                rst += "Exception information:" + Environment.NewLine;
                rst += EX.Message;
            }
            return rst;
        }

        public static string  Config_AddConfigFile(string fileName,bool isAdd)
        {
            string rst = string.Empty;
            try
            {
                Package p = GetPackage(fileName);
                foreach (var con in p.Configurations)
                {
                    p.Configurations.Remove(con);
                    if (File.Exists(con.ConfigurationString)
                        && !con.ConfigurationString.Contains(".DB.")
                        //Not remove .db. for others used
                        )
                    {
                       File.Delete(con.ConfigurationString); 
                    }
                }
                if (!isAdd)
                {
                    SavePackage(fileName, p);
                    return  "EXITCODE:0 Configuration file has removed success for Package:" + fileName + "\r\n"; ;
                }
                int count_UserVariabes = 0;
                p.EnableConfigurations = true;

                string cfgName = "Config_" + Path.GetFileNameWithoutExtension(fileName);//p.Name may duplicate
                string connstr = string.Format("{1}Config\\Config_{0}.dtsConfig",
                Path.GetFileNameWithoutExtension(fileName),
                Path.GetDirectoryName(fileName).ToUpper().Replace("DTSX", ""));
                Config_AddConfigFile(ref p, cfgName, connstr);
                string configFolder = Path.GetDirectoryName(fileName).ToUpper().Replace("DTSX", "Config");
                foreach (Variable v in p.Variables)
                {
                    if (
                        v.Namespace   != "System"
                        && v.DataType != TypeCode.Object
                        //Object type can not export 
                        )
                    {
                        v.Properties["Value"].SetExport(v, true);
                        count_UserVariabes++;
                    }
                }
                if (!Directory.Exists(configFolder))
                    Directory.CreateDirectory(configFolder);
                if (count_UserVariabes > 0)
                {
                    p.ExportConfigurationFile(connstr);
                }
                //Add another Configuration File For DB Connection
                foreach (var con in p.Connections)
                {
                    //find the related db configuration file in config folder
                    if (con.CreationName == "OLEDB")
                    {
                        //DS_SHARED IC_CLQR.DB. RZ_CLQR.DB.DTSCONFIG
                        cfgName = con.Name.Replace(" ", "_");
                        connstr = configFolder + "\\" + cfgName + ".DB.dtsConfig";
                        Config_AddConfigFile(ref p, cfgName, connstr);
                        if(!File.Exists(connstr))//if exists not create
                            p.ExportConfigurationFile(connstr);
                    }
                }
                SavePackage(fileName, p);
                rst += "EXITCODE:0 Configuration file has added success for Package:" + fileName + "\r\n";
            }
            catch (Exception EX)
            {
                rst = "EXITCODE:1 Configuration file add fail for Package:" + fileName+"\r\n";
                rst += "Exception information:\r\n";
                rst += EX.Message;
            }
            return rst;
        }

        static void Config_AddConfigFile(ref Package p, string cfgName, string connstr)
        {
            Microsoft.SqlServer.Dts.Runtime.Configuration config = p.Configurations.Add();
            config.Name = cfgName;
            config.ConfigurationType = DTSConfigurationType.ConfigFile;
            config.ConfigurationString = connstr;
        }

        public static string Config_GenerateBatch(ParameterObject _po)
        {
           string rst = string.Empty;
            try
            {
                Package p = GetPackage(_po._fileName);
                string batcontent = string.Format("DTEXEC /FILE \"{0}\"", _po._fileName);
                if (p.EnableConfigurations)
                {
                    foreach (var cf in p.Configurations)
                    {
                        if (cf.ConfigurationType.Equals(DTSConfigurationType.ConfigFile))
                            batcontent += string.Format(" /CONFIGFILE \"{0}\"", cf.ConfigurationString);
                    }
                }
                batcontent += " /CHECKPOINTING OFF /REPORTING EWCDI";
                string batchfolder = Path.GetDirectoryName(_po._fileName).ToUpper().Replace("DTSX", "Bat\\");
                if (!Directory.Exists(batchfolder))
                    Directory.CreateDirectory(batchfolder);
                string batfilename = batchfolder + "BAT_"
                    + Path.GetFileNameWithoutExtension(_po._fileName) //p.Name sometimes p.Name may duplicate
                    + ".bat";
                if (File.Exists(batfilename)) File.Delete(batfilename);
                File.AppendAllText(batfilename, batcontent);
                rst = "EXITCODE:0 Configuration generate batfile Success for Package:" + _po._fileName;
            }
            catch (Exception EX)
            {
                rst = "EXITCODE:1 Configuration generate batfile fail for Package:" + _po._fileName;
                rst += "\r\nException information:\r\n";
                rst += EX.Message;
            }
            return rst;
        }
        #endregion


    }


}
